Granting Permissions Using GRANT in MySQL – Detailed Notes

Course: MySQL Training Course

Topic: Granting Permissions Using GRANT

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to GRANT in MySQL

MySQL provides a privilege system that controls which operations each database user can perform. A user may be allowed to read data, insert records, update existing information, delete records, create tables, or administer database objects. These permissions help protect sensitive information and prevent unauthorized database operations.

The GRANT statement is used to assign privileges to MySQL user accounts and roles. A database administrator can use it to provide only the access required for a particular job, such as allowing an application account to read and update customer records without giving it permission to delete tables.

For example, an administrator can grant permission to read records from a database table using the following statement:

GRANT SELECT ON company_db.employees TO 'report_user'@'localhost';

This statement grants the SELECT privilege on the employees table in the company_db database to the MySQL account 'report_user'@'localhost'.

Important: The GRANT statement does not create a user account. In a typical workflow, the administrator creates an account with CREATE USER and then assigns its privileges with GRANT.

2. Learning Objectives

After studying this topic, you will be able to:

• Understand the purpose of the MySQL GRANT statement.
• Identify common MySQL privileges and their purposes.
• Understand global, database, table, and column-level permissions.
• Create a user account and grant appropriate permissions.
• Grant read-only and read-write access to database users.
• Grant permissions to multiple users and understand role-based access.
• Verify assigned privileges using SHOW GRANTS.
• Understand WITH GRANT OPTION and its security implications.
• Recognize common permission errors and apply least-privilege security practices.

3. What Are Privileges in MySQL?

A privilege is an authorization that permits a MySQL account to perform a specific action. MySQL checks privileges when a user attempts to access a database, table, column, or administrative feature.

For example, a user with the SELECT privilege can retrieve permitted records, while a user with the INSERT privilege can add new rows within the scope of that permission.

3.1 Common MySQL Privileges

The privileges available depend on the object level and the MySQL version. Administrative privileges such as FILE, PROCESS, and CREATE USER have special security implications and should not be granted casually.

4. Syntax of the GRANT Statement

The general syntax for granting privileges in MySQL is:

GRANT privilege_name [, privilege_name ...]
ON privilege_level
TO 'user_name'@'host_name';

4.1 Explanation of the Syntax

• GRANT: The SQL keyword used to assign privileges.
• privilege_name: The permission being granted, such as SELECT, INSERT, or UPDATE.
• ON: Specifies the database object or scope to which the privilege applies.
• privilege_level: Identifies the server, database, table, or supported column scope.
• TO: Specifies the account or accounts receiving the privileges.
• user_name: The name of the MySQL account.
• host_name: The host from which the account may connect, subject to account configuration.

4.2 A Simple Example

GRANT SELECT ON company_db.employees
TO 'report_user'@'localhost';

This statement allows the specified account to read data from the employees table in company_db. It does not automatically allow the account to insert, update, or delete records.

5. Understanding MySQL User Accounts

MySQL accounts are identified by a username and a host component. The host component helps determine where the account can connect from.

5.1 Account Format

'user_name'@'host_name'

Examples include:

'report_user'@'localhost'
'app_user'@'192.0.2.25'
'analyst'@'%.example.com'

• 'report_user'@'localhost' identifies an account configured for local connections.
• 'app_user'@'192.0.2.25' identifies an account associated with the specified host address.
• 'analyst'@'%.example.com' uses a host pattern to match hosts within the specified domain pattern.

Host patterns must be used carefully. Avoid broad host access unless it is necessary and protected by appropriate authentication and network controls. In MySQL, omitting the host component is equivalent to using '%' in the account specification; it should not be assumed to mean local-only access.

6. Creating a User Before Granting Permissions

Before granting privileges, an administrator commonly creates the required account with CREATE USER. Account creation and privilege assignment are separate operations.

6.1 Create a User Account

CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'ReplaceWithAStrongPassword!';

This example creates a MySQL account and sets an authentication password. Replace the example password with a unique, strong secret. Do not reuse this sample password in a real environment.

6.2 Grant Read Permission

GRANT SELECT ON company_db.employees
TO 'report_user'@'localhost';

6.3 Verify the Permission

SHOW GRANTS FOR 'report_user'@'localhost';

The output shows the privileges assigned to the account. It may also show account-related grants and role assignments, depending on the account configuration.

7. Understanding Privilege Levels

MySQL supports different privilege scopes. Choosing the correct scope is important because it determines how much of the database a user can access.

8. Granting Global Permissions

Global privileges apply across the MySQL server within the scope defined by the privilege. They can affect multiple databases and may include powerful administrative capabilities.

8.1 Grant a Global Read Privilege

GRANT SELECT ON *.*
TO 'global_reader'@'localhost';

This grants global SELECT access, subject to MySQL privilege rules and other applicable account restrictions. It is broader than granting access to a single database.

8.2 Grant All Global Privileges

GRANT ALL PRIVILEGES ON *.*
TO 'db_admin'@'localhost';

This grants all privileges available at the global level at the time of the statement, excluding GRANT OPTION and PROXY. It should only be used when the account genuinely needs broad server-level authority.

Security warning: Do not use global privileges for routine application accounts. A compromised account with broad permissions can expose or damage data across multiple databases.

9. Granting Database-Level Permissions

Database-level privileges apply to objects within a specific database, according to the privileges granted.

9.1 Grant Read Access to a Database

GRANT SELECT ON company_db.*
TO 'analyst'@'localhost';

The account can read permitted tables and views in the company_db database without receiving global read access to every database.

9.2 Grant Read and Write Permissions

GRANT SELECT, INSERT, UPDATE
ON company_db.*
TO 'app_user'@'localhost';

This allows the account to read data, add rows, and update existing rows within the granted database scope. It does not grant DELETE or permission to drop tables.

9.3 Grant Database-Level Privileges for Table Management

GRANT CREATE, ALTER, INDEX
ON company_db.*
TO 'developer'@'localhost';

This example grants specified table-management capabilities at the database level where supported. Only grant schema-changing permissions to users who need them.

10. Granting Table-Level Permissions

Table-level permissions are useful when a user should access one table but should not automatically receive the same permissions on every table in a database.

10.1 Grant SELECT on One Table

GRANT SELECT ON company_db.employees
TO 'hr_viewer'@'localhost';

The account receives permission to read the specified table, rather than every table in the database.

10.2 Grant Multiple Privileges on One Table

GRANT SELECT, INSERT, UPDATE
ON company_db.customers
TO 'customer_app'@'localhost';

The account can read, insert, and update records in the customers table. The statement does not grant deletion rights.

10.3 Grant DELETE on a Specific Table

GRANT DELETE ON company_db.temporary_records
TO 'cleanup_user'@'localhost';

This grants the ability to delete rows from the specified table. Because deletion can cause data loss, such access should be restricted and monitored.

11. Granting Column-Level Permissions

Column-level privileges restrict certain operations to specific columns. MySQL supports column-level grants for privileges such as SELECT, INSERT, and UPDATE.

11.1 Allow Reading Selected Columns

GRANT SELECT (employee_id, employee_name, department)
ON company_db.employees
TO 'basic_hr_viewer'@'localhost';

This grants column-level read permission for the listed columns. It can help limit exposure of fields such as salary, personal contact details, or other sensitive information, provided the account has no broader privilege that permits access to those fields.

11.2 Allow Updating One Column

GRANT UPDATE (department)
ON company_db.employees
TO 'department_manager'@'localhost';

This grants column-level update permission for the department column. It does not, by itself, grant permission to update every column in the table.

11.3 Allow Inserting Specific Columns

GRANT INSERT (employee_name, department)
ON company_db.employees
TO 'employee_entry_user'@'localhost';

This allows inserts that specify the permitted columns, subject to table constraints, defaults, and any other applicable privileges.

Important: Column-level restrictions do not override broader privileges already granted to the same account. Review all applicable global, database, table, and column grants when checking effective access.

12. Granting Multiple Privileges in One Statement

MySQL allows multiple privileges to be specified in a single GRANT statement. This makes it convenient to assign a set of related permissions.

GRANT SELECT, INSERT, UPDATE, DELETE
ON company_db.orders
TO 'order_manager'@'localhost';

This example grants the account read, insert, update, and delete permissions on the orders table.

Grant only the privileges required by the account's job. If the user only needs to view reports, granting SELECT is generally preferable to granting write privileges.

13. Granting ALL PRIVILEGES

The ALL PRIVILEGES option grants all privileges available at the specified privilege level, except GRANT OPTION and PROXY.

13.1 Grant All Privileges on One Database

GRANT ALL PRIVILEGES ON company_db.*
TO 'database_manager'@'localhost';

This grants all privileges available at the database level for company_db. It does not automatically grant every global-only administrative privilege.

13.2 Grant All Privileges on One Table

GRANT ALL PRIVILEGES ON company_db.products
TO 'product_manager'@'localhost';

This assigns all privileges available at the specified table level. It does not automatically include the ability to grant those privileges to other accounts.

13.3 Important Considerations

• ALL PRIVILEGES does not include GRANT OPTION.
• The privileges granted depend on the scope specified in the ON clause.
• Global-only administrative privileges are not automatically included in a database-level grant.
• Granting all privileges may give a user permission to change or delete important objects.
• Prefer a specific privilege list when the account has a clearly defined job.

14. Understanding WITH GRANT OPTION

The WITH GRANT OPTION clause allows a user to delegate privileges to other accounts within the scope and authority permitted by MySQL. It is different from simply receiving permission to access data.

14.1 Example

GRANT SELECT ON company_db.*
TO 'team_lead'@'localhost'
WITH GRANT OPTION;

This grants database-level SELECT privileges and permits the account to grant eligible privileges to other accounts. The account cannot grant privileges it does not possess or otherwise have authority to delegate.

14.2 Why This Option Requires Caution

• A user with delegation rights may grant access to other users.
• Privileges delegated by a trusted user can spread beyond the original intended group.
• Users may combine separately delegated permissions, increasing the total effective access.
• Administrative teams should review and audit delegation rights regularly.

Do not grant WITH GRANT OPTION to ordinary application accounts unless delegated access is an explicit requirement.

15. Granting Permissions to Multiple Users

A single privilege statement can grant the same privileges to multiple accounts.

GRANT SELECT ON company_db.*
TO 'analyst_one'@'localhost',
   'analyst_two'@'localhost';

Both accounts receive the specified database-level SELECT privilege. Each account remains a separate MySQL account with its own authentication and other privilege assignments.

16. Granting Roles in MySQL

A role is a named collection of privileges. Roles simplify permission management when multiple users need similar access.

For example, a company may create a reporting role that has read access to selected business data and then assign that role to several reporting users.

16.1 Create a Role

CREATE ROLE 'reporting_role';

16.2 Grant Privileges to the Role

GRANT SELECT ON company_db.*
TO 'reporting_role';

16.3 Assign the Role to a User

GRANT 'reporting_role'
TO 'analyst'@'localhost';

16.4 Set the Role as the Default Role

SET DEFAULT ROLE 'reporting_role'
TO 'analyst'@'localhost';

Role assignment and role activation are separate concepts. A granted role may need to be activated in the session unless it is configured as a default role. Effective access depends on active roles and other privileges already granted to the account.

17. Verifying Granted Permissions with SHOW GRANTS

After granting privileges, administrators should verify the account's permission configuration. MySQL provides the SHOW GRANTS statement for this purpose.

17.1 View the Current Account's Grants

SHOW GRANTS;

17.2 View a Specific Account's Grants

SHOW GRANTS FOR 'report_user'@'localhost';

17.3 Example of Expected Output

GRANT USAGE ON *.* TO `report_user`@`localhost`
GRANT SELECT ON `company_db`.`employees`
TO `report_user`@`localhost`

This illustrative output indicates that the account has a basic account entry and the specified table-level SELECT privilege. Actual output can differ based on the account's privileges, roles, authentication settings, and MySQL version.

USAGE is commonly displayed to represent the account's existence without implying additional data-access privileges in that grant line.

18. Practical Example: Employee Database Access Control

Consider a company database named company_db with an employees table. The administrator wants to create a reporting account that can read employee information but cannot modify it.

Step 1: Create the Database

CREATE DATABASE IF NOT EXISTS company_db;

Step 2: Create the Table

CREATE TABLE IF NOT EXISTS company_db.employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

Step 3: Create a Reporting User

CREATE USER 'employee_reporter'@'localhost'
IDENTIFIED BY 'ReplaceWithAStrongPassword!';

Step 4: Grant Read-Only Access

GRANT SELECT ON company_db.employees
TO 'employee_reporter'@'localhost';

Step 5: Verify the Grant

SHOW GRANTS FOR 'employee_reporter'@'localhost';

Step 6: Test the Permission

Connect to MySQL using the reporting account and execute a read query:

SELECT employee_id, employee_name, department
FROM company_db.employees;

If the account has the required access and the connection is correctly configured, this query can retrieve permitted records.

Step 7: Test a Restricted Operation

UPDATE company_db.employees
SET department = 'Finance'
WHERE employee_id = 101;

This update should fail with a permission error if the account has only the stated SELECT privilege and no additional applicable update privileges. This demonstrates how privileges restrict database operations.

19. Understanding GRANT and REVOKE

GRANT and REVOKE are complementary statements used to manage privileges.

To remove the read privilege previously granted to the account, use:

REVOKE SELECT ON company_db.*
FROM 'analyst'@'localhost';

Revoking a privilege at one level does not necessarily remove access granted at another level. For example, removing a database-level SELECT privilege does not eliminate a separate global SELECT privilege. Always inspect the account's complete privilege configuration.

20. Does GRANT Require FLUSH PRIVILEGES?

In modern MySQL versions, statements such as GRANT, REVOKE, CREATE USER, and ALTER USER update the privilege system directly. You normally do not need to execute FLUSH PRIVILEGES after using these statements.

FLUSH PRIVILEGES is associated with reloading privilege information in specific situations, such as when supported direct changes to grant tables have occurred. Directly editing MySQL system tables to manage permissions is generally discouraged; use account-management statements instead.

21. Common Errors When Using GRANT

22. Best Practices for Granting Permissions

• Follow least privilege: Grant only the permissions required for the user's responsibilities.
• Prefer specific scopes: Use table-level or database-level privileges instead of global access when possible.
• Avoid unnecessary ALL PRIVILEGES: Assign individual privileges when the user's requirements are known.
• Limit GRANT OPTION: Allow privilege delegation only to trusted administrators who need it.
• Use roles for groups: Assign shared permission sets through roles instead of maintaining repetitive grants for every user.
• Protect sensitive data: Consider column-level privileges and views where they meet the access requirements.
• Use separate accounts: Avoid sharing one database account among unrelated people or applications.
• Use strong authentication: Configure suitable authentication methods and protect credentials.
• Review permissions regularly: Remove access that is no longer needed.
• Test with the actual account: Confirm both allowed and denied operations.
• Avoid unnecessary administrative access: Privileges such as FILE, PROCESS, and server administration privileges can expose sensitive information or affect server availability.
• Keep an audit trail: Follow organizational procedures for recording and reviewing permission changes.

23. Real-World Applications of GRANT

23.1 Web Application

A website may use an application account with permission to read and update selected application tables. It should not automatically receive permission to administer the MySQL server.

23.2 Reporting and Analytics

Reporting users often need read-only access. Granting SELECT on approved databases or tables can help prevent accidental modifications to production data.

23.3 Human Resources Systems

HR staff may need access to employee records, while other departments may only need selected non-sensitive columns. Appropriate table- and column-level grants can help implement these requirements.

23.4 Development Environments

Developers may need permissions to create and alter objects in a development database. Those permissions should not automatically be extended to production systems.

23.5 Database Administration

Database administrators may require wider privileges to manage accounts, databases, backups, or server operations. Such accounts should be tightly controlled and used only for authorized administrative tasks.

24. Exercises for Practice

Complete the following exercises to practice granting MySQL permissions.

1. Create a user named sales_viewer that connects from localhost.
2. Grant SELECT permission on the sales table in the company_db database.
3. Grant SELECT and INSERT on the orders table to a suitable application account.
4. Grant UPDATE permission on only the department column of the employees table.
5. Use SHOW GRANTS to inspect a user's permissions.
6. Revoke a previously granted SELECT privilege and verify the result.
7. Create a reporting role, grant it database read access, and assign the role to a reporting user.
8. Explain why granting ALL PRIVILEGES ON *.* to an ordinary application account is risky.

25. Interview Questions and Answers

Q1. What is the GRANT statement in MySQL?

Answer: GRANT is used to assign privileges to MySQL user accounts or roles. It controls which database operations an account is authorized to perform.

Q2. Does GRANT create a user?

Answer: No. Account creation is commonly performed with CREATE USER. The GRANT statement assigns privileges to an existing account or assigns roles, depending on the syntax used.

Q3. What does SELECT permission allow?

Answer: It allows an account to read permitted data within the scope of the grant.

Q4. What is the difference between global and database-level permissions?

Answer: Global privileges use a scope such as *.* and can apply across the server. Database-level privileges use a scope such as company_db.* and apply within that database, according to the granted privilege.

Q5. What is the purpose of WITH GRANT OPTION?

Answer: It allows an account to delegate eligible privileges it is authorized to grant to other accounts. It should be given only when privilege delegation is required.

Q6. What is SHOW GRANTS used for?

Answer: It displays the privilege grants associated with an account or the current session, helping administrators inspect permission assignments.

Q7. What is the difference between GRANT and REVOKE?

Answer: GRANT assigns privileges, while REVOKE removes specified privileges or delegation rights.

Q8. Does GRANT ALL PRIVILEGES include GRANT OPTION?

Answer: No. ALL PRIVILEGES does not include GRANT OPTION or PROXY.

Q9. Why should global privileges be restricted?

Answer: Global privileges may provide access across many databases or grant powerful administrative capabilities. Excessive access increases the risk of data exposure, accidental changes, and misuse.

Q10. Is FLUSH PRIVILEGES required after GRANT?

Answer: Normally, no. MySQL applies privilege changes made through GRANT directly. Reloading privilege information is relevant in specific situations, not as a routine step after every grant.

Q11. What is a role in MySQL?

Answer: A role is a named collection of privileges that can be assigned to users or other roles. Roles simplify the management of common permission sets.

Q12. What is the least-privilege principle?

Answer: It means giving each account only the minimum permissions required to perform its assigned tasks.

26. Quick Revision Checklist

• GRANT assigns privileges or roles.
• CREATE USER creates an account.
• SELECT reads data.
• INSERT adds rows.
• UPDATE modifies rows.
• DELETE removes rows.
• *.* represents global scope.
• company_db.* represents database scope.
• company_db.employees represents a specific table or view.
• Column-level grants can restrict supported operations to specific columns.
• WITH GRANT OPTION permits eligible privilege delegation.
• SHOW GRANTS helps verify privilege assignments.
• REVOKE removes specified privileges.
• Roles group privileges for easier administration.
• Always check for broader privileges and active roles when troubleshooting access.
• Use the least-privilege principle to protect database security.

27. Conclusion

The MySQL GRANT statement is an essential tool for database access control. It enables administrators to assign appropriate permissions at global, database, table, column, and supported stored-routine levels. Correctly scoped grants help protect data, reduce accidental changes, and support secure application development.

For effective permission management, create accounts deliberately, grant only necessary privileges, use roles when appropriate, verify assignments with SHOW GRANTS, and review permissions regularly. Be particularly careful with global privileges, ALL PRIVILEGES, and WITH GRANT OPTION.

28. Additional Learning Resources

• Official MySQL Documentation – GRANT Statement
• Official MySQL Documentation – Privileges Provided by MySQL
• Official MySQL Documentation – Account Management Statements
• Explore the MySQL Training Course at JustAcademy
• Register for a MySQL Course Demo